/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.myapp.DAO;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;
import org.apt.Bean.NewEntities;
import org.apt.connectDB.MyConnect;

/**
 *
 * @author Administrator
 */
public class NewsDao {

    /**
     * Function Get All News
     * @return List News
     */
    public static List<NewEntities> getNews() {
        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;
        List<NewEntities> listNew = new ArrayList<NewEntities>();
        String sql = "select * from News ";

        try {
            connection = MyConnect.getConnection();
            statement = connection.createStatement();
            resultSet = statement.executeQuery(sql);
            while (resultSet.next()) {
                NewEntities news = new NewEntities();
                news.setNewID(resultSet.getInt(1));
                news.setTile(resultSet.getString(2));
                news.setSummary(resultSet.getString(3));
                news.setContent(resultSet.getString(4));
                news.setImage(resultSet.getString(5));
                news.setDaypost(resultSet.getString(6));
                news.setAuthor(resultSet.getString(7));
                news.setStatus(resultSet.getBoolean(8));
                news.setDescription(resultSet.getString(9));
                listNew.add(news);
            }
        } catch (SQLException ex) {
            Logger.getLogger(MyConnect.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            MyConnect.closeConntion(connection, statement, resultSet);
        }
        return listNew;
    }

    /**
     * Function get
     * @param pageno : number of page 
     * @return List 10 News
     */
    public static List<NewEntities> getNews(int pageno) {
        Connection con = null;
        Statement st = null;
        ResultSet rs = null;
        List<NewEntities> listNew = new ArrayList<NewEntities>();
        int pageno2;
        try {
            if (pageno <= 1) {
                pageno = 1;
            } else if (pageno == 2) {
                pageno = 10 + 1;
            } else if (pageno > 2) {
                pageno = (pageno - 1) * 10 + 1;
            }
            pageno2 = pageno + 9;
            con = MyConnect.getConnection();
            st = con.createStatement();
            String sql = "SELECT * FROM ( SELECT ROW_NUMBER() OVER(ORDER BY NewsID) AS row,* FROM News)  as tblTemp WHERE  row between " + pageno + " AND " + pageno2 + " ";
            rs = st.executeQuery(sql);
            while (rs.next()) {
                NewEntities news = new NewEntities();
                news.setNewID(rs.getInt("NewsID"));
                news.setTile(rs.getString("Title"));
                news.setSummary(rs.getString("Summary"));
                news.setContent(rs.getString("Content"));
                news.setImage(rs.getString("Image"));
                news.setDaypost(rs.getString("DayPost"));
                news.setAuthor(rs.getString("Author"));
                news.setStatus(rs.getBoolean("Status"));
                news.setDescription(rs.getString("Description"));
                listNew.add(news);
            }
        } catch (SQLException ex) {
            Logger.getLogger(LectureDao.class.getName()).log(Level.SEVERE, null, ex);
            System.out.println("ERROR:" + ex.getCause() + "");
        } finally {
            MyConnect.closeConntion(con, st, rs);
        }
        return listNew;
    }

    /**
     * Function Get one News
     * @param newsID
     * @return List One New
     */
    public static NewEntities getNewsByID(int newsID) {
        Connection con = null;
        Statement st = null;
        ResultSet rs = null;
        NewEntities news = new NewEntities();
        con = MyConnect.getConnection();
        rs = null;
        String sql = "select * from News where NewsID=" + newsID + "";
        try {
            st = con.createStatement();
            rs = st.executeQuery(sql);
            while (rs.next()) {
                news.setNewID(rs.getInt(1));
                news.setTile(rs.getString(2));
                news.setSummary(rs.getString(3));
                news.setContent(rs.getString(4));
                news.setImage(rs.getString(5));
                news.setDaypost(rs.getString(6));
                news.setAuthor(rs.getString(7));
                news.setStatus(rs.getBoolean(8));
                news.setDescription(rs.getString(9));
            }
        } catch (SQLException ex) {
            Logger.getLogger(MyConnect.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            MyConnect.closeConntion(con, st, rs);
        }
        return news;
    }

    /**
     * Delete News
     * @param newsID
     * @return
     */
    public static boolean DeleteNewsByID(int newsID) {
        boolean flag = false;
        Connection con = null;
        Statement st = null;
        ResultSet rs = null;
        NewEntities news = new NewEntities();
        con = MyConnect.getConnection();
        rs = null;
        String sql = "Delete from News where NewsID = " + newsID + "";
        try {
            NewEntities newsexist = getNewsByID(newsID);
            if (newsexist != null) {
                st = con.createStatement();
                int reruilt = st.executeUpdate(sql);
                if (reruilt > 0) {
                    flag = true;
                } else {
                    flag = false;
                }
            } else {
                flag = false;
            }
        } catch (SQLException ex) {
            Logger.getLogger(MyConnect.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            MyConnect.closeConntion(con, st, rs);
        }
        return flag;
    }

    /**
     * Function Search News
     * @param pageno, tile of new
     * @return List New
     */
    public static List<NewEntities> SearchNewsByName(String title) {
        Connection con = null;
        Statement st = null;
        ResultSet rs = null;
        List<NewEntities> listNew = new ArrayList<NewEntities>();
        try {
            con = MyConnect.getConnection();
            st = con.createStatement();
            String sql = "SELECT top(10)* FROM  News WHERE Title LIKE N'" + title + "%'";
            rs = st.executeQuery(sql);
            while (rs.next()) {
                NewEntities news = new NewEntities();
                news.setNewID(rs.getInt("NewsID"));
                news.setTile(rs.getString("Title"));
                news.setSummary(rs.getString("Summary"));
                news.setContent(rs.getString("Content"));
                news.setImage(rs.getString("Image"));
                news.setDaypost(rs.getString("DayPost"));
                news.setAuthor(rs.getString("Author"));
                news.setStatus(rs.getBoolean("Status"));
                news.setDescription(rs.getString("Description"));
                listNew.add(news);
            }
        } catch (SQLException ex) {
            Logger.getLogger(NewsDao.class.getName()).log(Level.SEVERE, null, ex);
            System.out.println("ERROR:" + ex.getCause() + "thao");
        } finally {
            MyConnect.closeConntion(con, st, rs);
        }
        return listNew;
    }

    /**
     * function get top 5 from news
     * @param 
     * @return list top 5 new
     */
    public static List<NewEntities> getTop5News() {
        Connection con = null;
        Statement st = null;
        ResultSet rs = null;
        List<NewEntities> listNew = new ArrayList<NewEntities>();
        try {
            con = MyConnect.getConnection();
            st = con.createStatement();
            String sql = "SELECT top(5)* FROM  News order by NewsID ";
            rs = st.executeQuery(sql);
            while (rs.next()) {
                NewEntities news = new NewEntities();
                news.setNewID(rs.getInt("NewsID"));
                news.setTile(rs.getString("Title"));
                news.setSummary(rs.getString("Summary"));
                news.setContent(rs.getString("Content"));
                news.setImage(rs.getString("Image"));
                news.setDaypost(rs.getString("DayPost"));
                news.setAuthor(rs.getString("Author"));
                news.setStatus(rs.getBoolean("Status"));
                news.setDescription(rs.getString("Description"));
                listNew.add(news);
            }
        } catch (SQLException ex) {
            Logger.getLogger(NewsDao.class.getName()).log(Level.SEVERE, null, ex);
            System.out.println("ERROR:" + ex.getCause() + "thao");
        } finally {
            MyConnect.closeConntion(con, st, rs);
        }
        return listNew;
    }

    public static void main(String[] args) {
        List<NewEntities> a = getTop5News();
        System.out.println(a.size() - 5);
    }
}
